﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.Sql;
using DTO;

namespace DAO
{
    public class DAONhanVien:DataProvider
    {
        public static DataTable checkLogin(DTOUser user)
        {
            string select = "select * from nhanvien where [username]=@username and [password]=@password";
            var da = new SqlDataAdapter(select, KetNoi());
            da.SelectCommand.Parameters.AddWithValue("@username", user.username);
            da.SelectCommand.Parameters.AddWithValue("@password", user.password);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DongKetNoi();
            return dt;
        }
        public static DataTable DSNhanVien(bool admin = true)
        {
            DataTable NhanVien;
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh;
            if (admin == false)
            {
                chuoilenh = "select * from nhanvien where usertype is null or usertype = 'normal'";
            }
            else
            {
                chuoilenh = "select * from nhanvien";
            }
            var da = new SqlDataAdapter(chuoilenh, MoKetNoi);            
            
            DataSet ds = new DataSet();
            da.Fill(ds);
            NhanVien = ds.Tables[0];
            DongKetNoi();
            return NhanVien;
        }
        public static int ThemNhanVien(DTOUser user)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "insert into nhanvien(username,password,name,diachi,sodienthoai,usertype,created) values(@user,@password,@name,@diachi,@sdt,@type,@created)";
            SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@user", user.username);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@password", user.password);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@name", user.fullname);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@diachi", user.address);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@sdt", user.phonenum);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@type", user.usertype);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@created", user.created);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            DongKetNoi();
            return result;

        }
        public static int XoaNhanVien(int id)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "Delete from nhanvien where MaNV=@id";
            SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@id", id);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            DongKetNoi();
            return result;
        }
        public static int CapNhatNhanVien(DTOUser nhanvien)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "update nhanvien set username=@user,password=@pass,name=@fullname,diachi=@diachi,sodienthoai=@sdt,usertype=@type,created=@date where MaNV=@id";
            SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@user", nhanvien.username);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@pass", nhanvien.password);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@fullname", nhanvien.fullname);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@diachi", nhanvien.address);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@sdt", nhanvien.phonenum);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@type", nhanvien.usertype);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@date", nhanvien.created);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@id", nhanvien.id);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            DongKetNoi();
            return result;
        }
    }

    
}
